/**
 * 
 */
package sgc.beans;

import java.io.File;
import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.component.html.HtmlDataTable;
import javax.faces.context.FacesContext;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletContext;
import javax.sql.DataSource;

import sgc.struc.Company;

/**
 * @author djauregu
 * 
 */
@ManagedBean(name = "companyBean")
@RequestScoped
public class CompanyBean implements Serializable {

	private static final long serialVersionUID = 1L;
	private DataSource ds;
	private Company company;
	private HtmlDataTable htmlDataTable;
	private int tabindex;

	/**
	 * 
	 */
	public CompanyBean() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sgcweb");
		} catch (NamingException e) {
			e.printStackTrace();
		}
		company = new Company();
	}

	public Company getCompany() {
		return company;
	}

	public void setCompany(Company company) {
		this.company = company;
	}

	public HtmlDataTable getHtmlDataTable() {
		return htmlDataTable;
	}

	public void setHtmlDataTable(HtmlDataTable htmlDataTable) {
		this.htmlDataTable = htmlDataTable;
	}

	public int getTabindex() {
		return tabindex;
	}

	public void setTabindex(int tabindex) {
		this.tabindex = tabindex;
	}

	public List<Company> getCompanyList() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetAllCompany() }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<Company> list = new ArrayList<Company>();

		while (result.next()) {
			Company aux_company = new Company();
			aux_company.setCode(result.getInt(2));
			aux_company.setName(result.getString(3));
			aux_company.setAddress_st(result.getString(4));
			aux_company.setAddress_no(result.getString(5));
			aux_company.setAddress_fl(result.getString(6));
			aux_company.setAddress_ap(result.getString(7));
			aux_company.setCity(result.getString(8));
			aux_company.setState(result.getString(9));
			aux_company.setCountry(result.getString(10));
			aux_company.setPostal_code(result.getString(11));
			aux_company.setTax_id_1(result.getString(12));
			aux_company.setTax_type_1(result.getString(13));
			aux_company.setTax_id_2(result.getString(14));
			aux_company.setTax_type_2(result.getString(15));
			aux_company.setStart_date(result.getDate(16));
			list.add(aux_company);
		}
		con.close();

		return list;
	}

	public List<String> getNameList() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetAllCompany() }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<String> list = new ArrayList<String>();
		String name;
		while (result.next()) {
			name = result.getString(2) + " - " + result.getString(3);
			list.add(name);
		}
		con.close();
		return list;
	}

	public String edit() {
		company = (Company) htmlDataTable.getRowData();
		tabindex = 0;
		return "OK";
	}

	public boolean check(int p_code) throws SQLException {
		int aux_cod;
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetCompany(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, p_code);
		cs.execute();
		ResultSet result = cs.getResultSet();
		result.next();
		aux_cod = result.getInt(1);
		con.close();

		if (aux_cod != 0) {
			return true;
		} else {
			return false;
		}
	}
	private java.sql.Date convertDate(java.util.Date jDate) {
		java.sql.Date sqlDate;
		long t;

		if (jDate == null) {
			sqlDate = new java.sql.Date(0);
		} else {
			t = jDate.getTime();
			sqlDate = new java.sql.Date(t);
		}
		return sqlDate;
	}
	
	public void save() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc;
		if (check(company.getCode()) == true) {
			simpleProc = "{ CALL sp_updCompany(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
		} else {
			simpleProc = "{ CALL sp_addCompany(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }";
		}

		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company.getCode());
		cs.setString(2, company.getName());
		cs.setString(3, company.getAddress_st());
		cs.setString(4, company.getAddress_no());
		cs.setString(5, company.getAddress_fl());
		cs.setString(6, company.getAddress_ap());
		cs.setString(7, company.getCity());
		cs.setString(8, company.getState());
		cs.setString(9, company.getCountry());
		cs.setString(10, company.getPostal_code());
		cs.setString(11, company.getTax_id_1());
		cs.setString(12, company.getTax_type_1());
		cs.setString(13, company.getTax_id_2());
		cs.setString(14, company.getTax_type_2());
		cs.setDate(15, convertDate(company.getStart_date()));
		
//
		ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance()
				.getExternalContext().getContext();
		String path = ctx.getRealPath("/private/" + company.getCode());
	       File dir=new File(path);
	       if(!dir.exists()){
	           dir.mkdir();
	       }
//
		
		cs.execute();
		ResultSet result = cs.getResultSet();
		result.next();
		String aux_message = result.getString(3);
		FacesContext.getCurrentInstance()
				.addMessage(
						null,
						new FacesMessage(FacesMessage.SEVERITY_INFO,
								aux_message, null));
		company = null;

	}
}
